let { db, query } = require('../database/db')

// 添加学生信息
// 字段：name,student_id,class_id,专业,enrol_date,graduate_date,ed_sys,ed_bkg,call_ed_bkg,state,phone,egy_person,
// egy_phone,native_place,address,email
// const createstudent = (name,student_id,class_id,enrol_date,graduate_date,ed_sys,ed_bkg,call_ed_bkg,state,phone,egy_person,
//     egy_phone,native_place,address,email)=>{
//     let sql = `INSERT INTO students (name,student_id,class_id,enrol_date,graduate_date,ed_sys,ed_bkg,call_ed_bkg,state,
//     phone,egy_person,egy_phone,native_place,address,email) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`
//     return query (sql,[name,student_id,class_id,enrol_date,graduate_date,ed_sys,ed_bkg,call_ed_bkg,state,phone,egy_person,
//     egy_phone,native_place,address,email])
// }

// module.exports = {
//     createstudent,
// }
// 运用ES6的class类的方法

class StudentsModel {
    // 添加用户信息
    // static async createstudent(student) {
    //     let sql = `
    //     INSERT INTO students 
    //     (name,gender,student_id,class_id,enrol_date,graduate_date,ed_sys,ed_bkg,call_ed_bkg,
    //     state,phone,egy_person,egy_phone,native_place,address,email) 
    //     VALUES 
    //     (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`
    //     return query(sql, [...student])
    // }
    // 生成学号
    static generateStudentNumber(id) {
        const now = new Date();
        const year = now.getFullYear();
        const month = String(now.getMonth() + 1).padStart(2, '0');

        // 生成4位随机字母 (A-Z)
        const randomLetters = Array(4).fill(0).map(() =>
            String.fromCharCode(65 + Math.floor(Math.random() * 26))
        ).join('');

        // 组合学号格式：WY + 年份 + 月份 + ID + 随机字母
        return `WY${year}${month}${String(id).padStart(4, '0')}${randomLetters}`;
    };
    static async createstudent(student) {
        console.log("model中的参数：：", student)
        // 1. 先插入基础数据（不带学号）
        const insertSql = `
        INSERT INTO students 
        (name, gender, class_id, enrol_date, graduate_date, 
         ed_sys, ed_bkg, call_ed_bkg, state, phone, 
         egy_person, egy_phone, native_place, address, email) 
        VALUES 
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        // name, gender, student_id, class_id, enrol_date, graduate_date, ed_sys,
        //     ed_bkg, call_ed_bkg, state, phone, egy_person, egy_phone, native_place, address, email
        const insertParams = [
            student.name,
            student.gender,
            student.class_id,
            student.enrol_date,
            student.graduate_date,
            student.ed_sys,
            student.ed_bkg,
            student.call_ed_bkg,
            student.state || 0,  // 默认状态为有效
            student.phone,
            student.egy_person,
            student.egy_phone,
            student.native_place,
            student.address,
            student.email
        ];

        // 执行插入
        const result = await query(insertSql, insertParams);
        const insertedId = result.insertId;  // 获取自增ID

        // 2. 生成含ID的学号
        const studentId = this.generateStudentNumber(insertedId);

        // 3. 更新学号字段
        const updateSql = `UPDATE students SET student_id = ? WHERE id = ?`;
        await query(updateSql, [studentId, insertedId]);

        return studentId;  // 返回生成的完整学号
    }
    // 删除某个用户信息--软删除
    static delete_student_f(id) {
        let sql = `UPDATE students SET state_del = 1 WHERE id = ?`
        return query(sql, id)
    }

    static start_state(id) {
        let sql = `UPDATE students SET state_del = 0 WHERE id = ?`
        return query(sql, id)
    }

    // 删除某个用户信息--真删除
    static delete_student_t(id) {
        let sql = `DELETE FROM students EHERE id = ?`
        return query(sql, id)
    }
    // 点击查看学生详细信息 && 学生信息修改--获取数据
    static student_view(id) {
        let sql = `
        SELECT students.id, students.name, students.student_id, students.phone, students.class_id, 
                   students.ed_bkg, students.ed_sys, students.enrol_date, students.graduate_date,
                   students.egy_person, students.egy_phone, students.state, students.gender,students.email,
                   students.native_place,students.address,students.call_ed_bkg,
                   classes.name as class_name, programs.name as program_name 
                   FROM students 
                   JOIN classes ON students.class_id = classes.id 
                   JOIN programs ON classes.program_id = programs.id
        WHERE students.id = ?`
        return query(sql, id)
    }
    // 学生信息修改--保存数据
    static student_set(student) {
        let sql = `UPDATE students SET name=?, gender=?, class_id=?, enrol_date=?, graduate_date=?, ed_sys=?, 
        ed_bkg=?, call_ed_bkg=?, state=?, phone=?, egy_person=?, egy_phone=?, native_place=?, address=?, email=? WHERE id 
        = ?`
        return query(sql, [...student])
    }
    // 学生信息列表搜索+分页功能
    // name,student_id,phone,class_id,program_id,ed_sys,state
    static async student_list(name, student_id, phone, class_id, program_id, ed_sys, state, page, page_size) {
        // 基础SQL查询语句，选择students表中的字段以及通过JOIN查询classes和programs表中的name字段
        let sql = `SELECT students.id, students.name, students.student_id, students.phone, students.class_id, 
                   students.ed_bkg, students.ed_sys, students.enrol_date, students.graduate_date,students.state_del,
                   students.egy_person, students.egy_phone, students.state,
                   classes.name as class_name, programs.name as program_name 
                   FROM students 
                   JOIN classes ON students.class_id = classes.id 
                   JOIN programs ON classes.program_id = programs.id`;

        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = [`0 = 0`]; // 默认条件，假设state_del为0表示激活状态
        if (name) {
            conditions.push(`students.name LIKE ?`);
            params.push(`%${name}%`);
        }
        if (student_id) {
            conditions.push(`students.student_id = ?`);
            params.push(student_id);
        }
        if (phone) {
            conditions.push(`students.phone = ?`);
            params.push(phone);
        }
        if (class_id) {
            conditions.push(`students.class_id = ?`);
            params.push(class_id);
        }
        if (program_id) {
            conditions.push(`classes.program_id = ?`);
            params.push(Number(program_id));
        }
        if (ed_sys) {
            conditions.push(`students.ed_sys = ?`);
            params.push(ed_sys);
        }
        if (state) {
            conditions.push(`students.state = ?`);
            params.push(state);
        }

        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 1) { // 因为默认条件已经添加了一个
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        sql += ` ORDER BY id DESC`
        // 添加分页的SQL语句
        sql += ` LIMIT ? OFFSET ?`;


        // 添加分页参数
        params.push(page_size);
        params.push((page - 1) * page_size);

        // 执行查询
        const [students, count] = await Promise.all([
            query(sql, params), // 查询学生列表
            query(`SELECT COUNT(*) as count FROM students 
                   JOIN classes ON students.class_id = classes.id 
                   JOIN programs ON classes.program_id = programs.id
                   WHERE ${conditions.map(c => `(${c})`).join(' AND ')}`, params) // 查询总数，确保使用相同的搜索条件
        ]);
        return {
            students: students, // 学生列表
            total: count[0].count // 总数，确保从结果中正确获取总数
        }
    }
    // 总学生数
    static all_student() {
        let sql = `SELECT COUNT(*) as count FROM students`
        return query(sql)
    }
    // 全部班级
    static async all_class() {
        let sql = `
            SELECT name, id
            FROM classes GROUP BY id 
        `
        let results = await query(sql)
        return results;
    }
    // 全部专业
    static async all_program() {
        let sql = `
            SELECT name, id
            FROM programs GROUP BY id 
        `
        let results = await query(sql)
        return results;
    }
    // 根据专业获取班级
    static async pro_class(program_id) {
        let sql = `
            SELECT name, id
            FROM classes
            WHERE classes.program_id = ?
        `
        let results = await query(sql, [program_id])
        return results;
    }

    // 查找所有考试
    static async all_exams(class_id, program_id) {
        let sql = `
            SELECT e.name, e.id
            FROM exams e
            LEFT JOIN classes c ON c.id = e.class_id
            LEFT JOIN programs p ON p.id = c.program_id
            WHERE 1=1
        `;
        let params = [];

        if (class_id) {
            sql += ` AND c.id = ?`;
            params.push(class_id);
        }
        if (program_id) {
            sql += ` AND p.id = ?`;
            params.push(program_id);
        }

        let results = await query(sql, params);
        return results;
    }
    
}

module.exports = StudentsModel;